CREATE VIEW [dbo].[vExperienceCountSummary]
AS
SELECT
    [dbo].[CustomerExperience].[CustomerExperienceKey],
    [dbo].[CustomerExperience].[UserKey],
    [dbo].[vOfferedCustomerExperience].[ProgramKey],
    [dbo].[vOfferedCustomerExperience].[ProgramName],
    [dbo].[vOfferedCustomerExperience].[ExperienceDefinitionKey],
    [dbo].[vOfferedCustomerExperience].[ExperienceDefinitionName],
    [dbo].[vOfferedCustomerExperience].[OfferingKey],
    [dbo].[vOfferedCustomerExperience].[OfferingName],
    [dbo].[vOfferedCustomerExperience].[OfferingName] AS Name,
    [dbo].[vOfferedCustomerExperience].[OfferingDesc] AS Description,
    [dbo].[vOfferedCustomerExperience].[OfferingDate] AS Date,
    [dbo].[vOfferedCustomerExperience].[ContactKey] AS Provider,
    [dbo].[vOfferedCustomerExperience].[LocationKey],
    [dbo].[vOfferedCustomerExperience].[LocationName],
    [dbo].[vOfferedCustomerExperience].[RequiresGradeFlag],
    [dbo].[vOfferedCustomerExperience].[RequiresApprovalFlag],
    [dbo].[vOfferedCustomerExperience].[NumberOfUnits],
    [dbo].[vOfferedCustomerExperience].[UomKey]

FROM
    [dbo].[CustomerExperience]
        INNER JOIN [dbo].[vOfferedCustomerExperience] ON [dbo].[CustomerExperience].[CustomerExperienceKey] = [dbo].[vOfferedCustomerExperience].[OfferedCustomerExperienceKey]

UNION ALL
SELECT
    [CustomerExperience1].[CustomerExperienceKey],
    [CustomerExperience1].[UserKey],
    [dbo].[vUnofferedCustomerExperience].[ProgramKey],
    [dbo].[vUnofferedCustomerExperience].[ProgramName],
    [dbo].[vUnofferedCustomerExperience].[ExperienceDefinitionKey],
    [dbo].[vUnofferedCustomerExperience].[ExperienceDefinitionName],
    NULL AS OfferingKey,
    NULL AS OfferingName,
    [dbo].[vUnofferedCustomerExperience].[ExperienceDefinitionName] AS Name,
    [dbo].[vUnofferedCustomerExperience].[ExperienceDefinitionDesc] AS Description,
    [dbo].[vUnofferedCustomerExperience].[ExperienceDefinitionDate] AS Date,
    NULL AS Provider,
    NULL AS LocationKey,
    [dbo].[vUnofferedCustomerExperience].[ExperienceDefinitionLocation] AS LocationName,
    [dbo].[vUnofferedCustomerExperience].[RequiresGradeFlag],
    [dbo].[vUnofferedCustomerExperience].[RequiresApprovalFlag],
    [dbo].[vUnofferedCustomerExperience].[NumberOfUnits],
    [dbo].[vUnofferedCustomerExperience].[UomKey]

FROM
    [dbo].[CustomerExperience] AS CustomerExperience1
        INNER JOIN [dbo].[vUnofferedCustomerExperience] ON [CustomerExperience1].[CustomerExperienceKey] = [dbo].[vUnofferedCustomerExperience].[UnofferedCustomerExperienceKey]

UNION ALL
SELECT
    [CustomerExperience2].[CustomerExperienceKey],
    [CustomerExperience2].[UserKey],
    [dbo].[vUserDefinedCustomerExperience].[ProgramKey],
    [dbo].[vUserDefinedCustomerExperience].[ProgramName],
    NULL AS ExperienceDefinitionKey,
    NULL AS ExperienceDefinitionName,
    NULL AS OfferingKey,
    NULL AS OfferingName,
    [dbo].[vUserDefinedCustomerExperience].[UserDefinedCustomerExperienceName] AS Name,
    [dbo].[vUserDefinedCustomerExperience].[UserDefinedCustomerExperienceDesc] AS Description,
    [dbo].[vUserDefinedCustomerExperience].[CompletionDate] AS Date,
    NULL AS Provider,
    NULL AS LocationKey,
    [dbo].[vUserDefinedCustomerExperience].[UserDefinedCustomerExperienceLocation] AS LocationName,
    NULL AS RequiresGradeFlag,
    [dbo].[vUserDefinedCustomerExperience].[RequiresApprovalFlag],
    [dbo].[vUserDefinedCustomerExperience].[UserDefinedCustomerExperienceUnits],
    [dbo].[vUserDefinedCustomerExperience].[UomKey]

FROM
    [dbo].[CustomerExperience] AS [CustomerExperience2]
        INNER JOIN [dbo].[vUserDefinedCustomerExperience] ON [CustomerExperience2].[CustomerExperienceKey] = [dbo].[vUserDefinedCustomerExperience].[UserDefinedCustomerExperienceKey]

GO